package cn.itcast.goods.category.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import cn.itcast.goods.category.domain.Category;
import cn.mytools.commons.CommonUtils;
import cn.mytools.jdbc.TxQueryRunner;

/**
 * 分类持久层
 * 
 * @author HL
 *
 */
public class CategoryDao {
	private QueryRunner qr = new TxQueryRunner();

	/**
	 * 返回所有分类
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Category> findAll() throws SQLException {
		String sql = "select * from t_category where pid is null order by orderBy";
		List<Map<String, Object>> mapList = qr.query(sql, new MapListHandler());
		List<Category> parents = toCategoryList(mapList);// 先得到所有的父分类

		for (Category parent : parents) { // 循环遍历每个父分类
			List<Category> children = findByParent(parent.getCid());// 查询子分类
			parent.setChildren(children);
		}

		return parents;
	}

	/**
	 * 通过父分类查询子分类
	 * 
	 * @param pid
	 * @return
	 * @throws SQLException
	 */
	public List<Category> findByParent(String pid) throws SQLException {
		String sql = "select * from t_category where pid=? order by orderBy";
		List<Map<String, Object>> mapList = qr.query(sql, new MapListHandler(), pid);
		return toCategoryList(mapList);
	}

	/**
	 * 把一个map中的数据映射到Category中
	 * 
	 * @param map
	 * @return
	 */
	private Category toCategory(Map<String, Object> map) {
		Category category = CommonUtils.toBean(map, Category.class);
		String pid = (String) map.get("pid");
		if (pid != null) { // 如果父分类ID不为空,
			Category parent = new Category();
			parent.setCid(pid);// 使用一个父分类来装载pid
			category.setParent(parent);// 再保存此父分类，父分类中只有pid一个属性
		}
		return category;
	}

	/**
	 * 把多个map映射成List<Category>
	 * 
	 * @param mapList
	 * @return
	 */
	private List<Category> toCategoryList(List<Map<String, Object>> mapList) {
		List<Category> categoryList = new ArrayList<Category>();

		for (Map<String, Object> map : mapList) {
			Category c = toCategory(map);
			categoryList.add(c);
		}
		return categoryList;
	}

	/**
	 * 添加分类
	 * 
	 * @param category
	 * @throws SQLException
	 */
	public void addCategory(Category category) throws SQLException {
		String sql = "insert into t_category(cid,cname,pid,`desc`) values(?,?,?,?)";
		String pid = null;
		if (category.getParent() != null) {
			pid = category.getParent().getCid();
		}
		Object[] params = { category.getCid(), category.getCname(), pid, category.getDesc() };
		qr.update(sql, params);

	}

	public List<Category> findAllParents() throws SQLException {
		String sql = "select * from t_category where pid is null order by orderBy";
		List<Category> parents = qr.query(sql, new BeanListHandler<Category>(Category.class));
		return parents;
	}

	public Category findByCid(String cid) throws SQLException {
		String sql = "select * from t_category where cid = ? order by orderBy";
		Map<String, Object> map = qr.query(sql, new MapHandler(), cid);
		return toCategory(map);
	}

	public void updateCategory(Category category) throws SQLException {
		String sql = "update t_category set `cname`=? ,`pid`=?,`desc`=? where `cid`=?";
		String pid = null;
		if (category.getParent() != null) {
			pid = category.getParent().getCid();
		}
		Object[] params = { category.getCname(), pid, category.getDesc(), category.getCid() };
		qr.update(sql, params);
	}

	public Object deleteCategory(String cid) throws SQLException {
		String sql = "delete from t_category where cid = ?";
		return qr.update(sql, cid);
	}

	public List<Category> findAllChildren(String cid) throws SQLException {
		String sql = "select * from t_category where pid = ? order by orderBy";
		List<Category> children = qr.query(sql, new BeanListHandler<Category>(Category.class), cid);
		return children;
	}
}
